
//
// 130128 - AH - Created.
// 130501a- AH - Now set property methods throw GimNoPropertyDefinedException if 
//               property is not defined.
// 130501b- AH - Fixed that table propertydef was not updated by define 
//               property method.
// 130502 - AH - All IOException objects created for a SQLException now get
//               the message of the sql exception.
// 130503 - AH - queryItems(String).
// 130504a- AH - Now all SQL statement included in error messages.
// 130504b- AH - Replaced Collection with List as type request methods returns.
// 130504c- AH - setProperty(String, String, Object) now do not throw exception
//               if property does not exist but define in default way.
// 130517 - AH - getRelations(int, String).
//

package org.japril.gim.sql;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.japril.gim.AbstractItems;
import org.japril.gim.DefaultItem;
import org.japril.gim.DefaultItemItem;
import org.japril.gim.Item;
import org.japril.gim.ItemItem;
import org.japril.gim.Items;
import org.japril.jutil.CharArrayPrintWriter;

/**
 * <p>
 *   Implementation of 
 *   {@link Items} using a RDBMS to store Generic Item Model data.
 * </p>
 * @author Arne Halvorsen (AH)
 */
public final class SqlItems extends AbstractItems
{
  private final Connection m_con;
  
  private final List<String> m_sqlLog=new ArrayList<String>();
  
  private void logSql(String sql)
  {
    m_sqlLog.add(sql);
  }
  
  /**
   * <p>
   *   Represents definition of a properties.
   * </p>
   * @author Arne Halvorsen (AH)
   */
  public final static class PropertyDef
  {
    /**
     * <p>
     *   Property's qualified name.
     * </p>
     */
    private final String m_name;
    
    /**
     * <p>
     *   Property's database type.
     * </p>
     */
    private final String m_ftype;
    
    /**
     * <p>
     *   Property's database field length, {@code null} if does not apply or is
     *   default.
     * </p>
     */
    private final Integer m_flen;
    
    /**
     * <p>
     *   Constructor.
     * </p>
     * @param name  {@link #m_name}.
     * @param ftype {@link #m_ftype}.
     * @param flen  {@link #m_flen}.
     */
    PropertyDef(String name, String ftype, Integer flen)
    {
      m_name=name;
      m_ftype=ftype;
      m_flen=flen;
    }
    
    /**
     * <p>
     *   Gets qualified name of property.
     * </p>
     * @return Name.
     */
    public String getName(){ return m_name; }
    
    /**
     * <p>
     *   Gets type of property in persistent database.
     * </p>
     * @return Type.
     */
    public String getFieldType(){ return m_ftype; }
    
    /**
     * <p>
     *   Gets length of field for property in persistent database.
     * </p>
     * @return Length if apply or {@code null} if does not apply or use default.
     */
    public Integer getFieldLength(){ return m_flen; }
    
    @Override
    public String toString()
    {
      StringBuilder sb=new StringBuilder();
      
      sb.append("name=").append(m_name).append(", ftype=").append(m_ftype)
        .append(", flen=").append(m_flen);
      
      return sb.toString();
    }
    
  }
  
  private final Map<String, PropertyDef> m_propDefs=
    new HashMap<String, PropertyDef>();
  
  private static final PropertyDef[] EMPTY_PROPERTY_DEF_ARRAY=
    new PropertyDef[0];
  
  /**
   * <p>
   *   Gets
   *   {@link PropertyDef}s.
   * </p>
   * @return {@link PropertyDef}s.
   */
  public PropertyDef[] getPropertyDefs()
  { 
    return m_propDefs.values().toArray(EMPTY_PROPERTY_DEF_ARRAY);
  }
  
  // Invoked from constructor to read property definitions.
  private void readPropDefs() throws IOException
  {
    Statement st=null;
    ResultSet rs=null;
    
    String sql="select * from propertydef";
    try
    {
      st=m_con.createStatement();
      logSql(sql);
      rs=st.executeQuery(sql);
      while (rs.next())
      {
        String name=rs.getString(1);
        String ftype=rs.getString(2);
        Integer flen=rs.getInt(3);
        flen=rs.wasNull() ? null : flen;
        m_propDefs.put(name, new PropertyDef(name, ftype, flen));
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }
  
  /**
   * <p>
   *   Constructor.
   * </p>
   * @param con Database connection.
   * @throws IOException if fails.
   */
  public SqlItems(Connection con) throws IOException
  {
    if (con==null)
    {
      throw new NullPointerException("con");
    }
    
    m_con=con;
    readPropDefs();
    
    PropertyDef[] pdefs=getPropertyDefs();
    for (PropertyDef pd : pdefs)
    {
      System.err.println("pdefs: "+pd);  
    }
  }

  // Specified in Items.
  public Item create(String type) throws IOException
  {
    type=(type==null) ? "" : type.trim();
    type=(type.length()==0) ? "item" : type;
    
    if (type.length()>256)
    {
      throw new IllegalArgumentException("type.length>256 : type.length()=="+
        type.length());
    }
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      sb.append("insert into item (type) values ('").append(type).append("')");
      sql=sb.toString();
      logSql(sql);
      st.execute(sql, Statement.RETURN_GENERATED_KEYS);
      rs=st.getGeneratedKeys();
      if (rs.next())
      {
        return new DefaultItem(rs.getInt(1), type);
      }
      
      throw new IOException("No key returned");
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }
  
  // Specified in Items.
  public boolean delete(int id) throws IOException
  {
    if (id<0)
    {
      throw new IllegalArgumentException("id<0 : "+id);
    }
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      
      sb.append("delete from itemitem where lid=").append(id)
        .append(" or rid=").append(id);
      sql=sb.toString();
      logSql(sql);
      st.executeUpdate(sql);
      
      sb.setLength(0);
      sb.append("delete from item where id=").append(id);
      sql=sb.toString();
      logSql(sql);
      int n=st.executeUpdate(sql);
      
      return n>0;
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }

  // Specified in Items.
  public boolean exists(int id) throws IOException
  {
    if (id<0)
    {
      throw new IllegalArgumentException("id<0 : "+id);
    }
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      sb.append("select count(*) as rowcount from item where id=").append(id);
      sql=sb.toString();
      logSql(sql);
      rs=st.executeQuery(sql);
      rs.next();
      switch (rs.getInt(1))
      {
        case 0 : return false;
        case 1 : return true;
        default : throw new Error("duplicates");
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }
  
  // Specified in Items.
  public Item getItem(int id) throws IOException
  {
    if (id<0)
    {
      throw new IllegalArgumentException("id<0 : "+id);
    }
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      sb.append("select type from item where id=").append(id);
      sql=sb.toString();
      logSql(sql);
      rs=st.executeQuery(sql);
      if (rs.next())
      {
        return new DefaultItem(id, rs.getString(1));  
      }
      else
      {
        return null;
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }
  
  // Specified in Items.
  public List<Item> getItems(String type) throws IOException
  {
    type=(type==null) ? "" : type.trim();
    type=(type.length()==0) ? null : type;
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    List<Item> retVal=new ArrayList<Item>();
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      
      sb.append("select id, type from item");
      if (type!=null) sb.append(" where type='").append(type).append("'");
      
      sql=sb.toString();
      logSql(sql);
      rs=st.executeQuery(sql);
      while (rs.next())
      {
        int id=rs.getInt(1);
        String t=rs.getString(2);
        retVal.add(new DefaultItem(id, t));
      }
      
      return retVal;
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }
  
  // Specified in Items.
  public List<Item> getItemsWithProperty(String name, Object val, String type)
    throws IOException
  {
    if (val==null)
    {
      throw new NullPointerException("val");
    }
    
    List<Item> retVal=new ArrayList<Item>();
    
    type=(type==null) ? "" : type.trim();
    type=(type.length()==0) ? "item" : type;
    
    String qName=getQualityPropertyName(name, type);
    PropertyDef pd=m_propDefs.get(qName);
    if (pd==null)
    {
      return retVal;
    }
    
    String quote=pd.getFieldType().equalsIgnoreCase("varchar") ? "'" : "";
    
    StringBuilder sb=new StringBuilder();
    sb.append("select item.id from item, ").append(qName)
      .append(" where item.id=").append(qName).append(".id and item.type='")
      .append(type).append("' and ").append(qName).append(".value=")
      .append(quote).append(val).append(quote);
    String sql=sb.toString();
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      logSql(sql);
      rs=st.executeQuery(sql);
      while (rs.next())
      {
        int id=rs.getInt(1);
        retVal.add(new DefaultItem(id, type));
      }
      
      return retVal;
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }

  // Specified in Items.
  public void createRelation(int lid, String lrole, int rid, String rrole)
    throws IOException
  {
    if (lid<0)
    {
      throw new IllegalArgumentException("lid<0 : "+lid);
    }
    if (rid<0)
    {
      throw new IllegalArgumentException("rid<0 : "+rid);
    }
    
    if (!exists(lid))
    {
      throw new IllegalArgumentException("left item do not exists");
    }
    if (!exists(rid))
    {
      throw new IllegalArgumentException("right item do not exists");
    }
    
    lrole=(lrole==null) ? "" : lrole.trim();
    lrole=(lrole.length()==0) ? "left" : lrole;
    
    rrole=(rrole==null) ? "" : rrole.trim();
    rrole=(rrole.length()==0) ? "right" : rrole;
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    try
    {
      st=m_con.createStatement();
      sb.append("insert into itemitem values (").append(lid).append(", '")
        .append(lrole).append("', ").append(rid).append(", '").append(rrole)
        .append("')");
      sql=sb.toString();
      logSql(sql);
      st.executeUpdate(sql);
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }  
  }
  
  public boolean deleteRelation(int lid, String lrole, int rid, String rrole)
    throws IOException
  {
    if (lid<0)
    {
      throw new IllegalArgumentException("lid<0 : "+lid);
    }
    if (rid<0)
    {
      throw new IllegalArgumentException("rid<0 : "+rid);
    }
    
    if (!exists(lid))
    {
      throw new IllegalArgumentException("left item do not exists");
    }
    if (!exists(rid))
    {
      throw new IllegalArgumentException("right item do not exists");
    }
    
    lrole=(lrole==null) ? "" : lrole.trim();
    lrole=(lrole.length()==0) ? "left" : lrole;
    
    rrole=(rrole==null) ? "" : rrole.trim();
    rrole=(rrole.length()==0) ? "right" : rrole;
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    try
    {
      st=m_con.createStatement();
      sb.append("delete from itemitem where lid=").append(lid)
        .append(" and ltype='").append(lrole).append("' and rid=").append(rid)
        .append(" and rtype='").append(rrole).append("'");
      sql=sb.toString();
      logSql(sql);
      int n=st.executeUpdate(sql);
      return n>0;
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }

  // Specified in Items.
  public List<ItemItem> getRelations() throws IOException
  {
    String sql="select lid, ltype, rid, rtype from itemitem";
    return queryItemItem(sql);
  }
  
  // Specified in Items.
  public List<ItemItem> getRelations(int lid, String rtype) throws IOException 
  {
    StringBuilder sb=new StringBuilder();
    sb.append("select itemitem.lid, itemitem.ltype, itemitem.rid, "+
              "itemitem.rtype from item, itemitem where lid=")
      .append(lid).append(" and item.id=itemitem.rid and item.type='")
      .append(rtype).append("'");
    String sql="select lid, ltype, rid, rtype from itemitem";
    return queryItemItem(sql);
  }
  
  private List<ItemItem> queryItemItem(String sql) throws IOException
  {
    Statement st=null;
    ResultSet rs=null;
    
    try
    {
      st=m_con.createStatement();
      
      logSql(sql);
      rs=st.executeQuery(sql);
      
      List<ItemItem> retVal=new ArrayList<ItemItem>();
      
      while (rs.next())
      {
        retVal.add(new DefaultItemItem(rs.getInt(1), rs.getString(2), 
          rs.getInt(3), rs.getString(4)));
      }
      
      return retVal;
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }

  // Specified in Items.
  public Item getRightItem(int lid, String rrole) throws IOException
  {
    if (lid<0)
    {
      throw new IllegalArgumentException("lid<0 : "+lid);
    }
    
    rrole=(rrole==null) ? "" : rrole.trim();
    rrole=(rrole.length()==0) ? null : rrole;
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      sb.append("select item.type, itemitem.rid from item, itemitem where ")
        .append("item.id=itemitem.rid and itemitem.lid=").append(lid);
      if (rrole!=null) sb.append(" and rtype='").append(rrole).append("'");
      
      sql=sb.toString();
      logSql(sql);
      rs=st.executeQuery(sql);
      if (rs.next())
      {
        String type=rs.getString(1);
        int id=rs.getInt(2);
        return new DefaultItem(id, type);
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
    
    return null;
  }
  
  // Specified in Items.
  public List<Item> getRightItems(int lid, String rrole) throws IOException
  {
    if (lid<0)
    {
      throw new IllegalArgumentException("lid<0 : "+lid);
    }
    
    rrole=(rrole==null) ? "" : rrole.trim();
    rrole=(rrole.length()==0) ? null : rrole;
    
    List<Item> retVal=new ArrayList<Item>();
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      sb.append("select item.type, itemitem.rid from item, itemitem where ")
        .append("item.id=itemitem.rid and itemitem.lid=").append(lid);
      if (rrole!=null) sb.append(" and rtype='").append(rrole).append("'");
      
      sql=sb.toString();
      logSql(sql);
      rs=st.executeQuery(sql);
      while (rs.next())
      {
        String type=rs.getString(1);
        int id=rs.getInt(2);
        retVal.add(new DefaultItem(id, type));
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
    
    return retVal;
  }
  
  // Specified in Items.
  public Item getLeftItem(int rid, String lrole) throws IOException
  {
    if (rid<0)
    {
      throw new IllegalArgumentException("rid<0 : "+rid);
    }
     
    lrole=(lrole==null) ? "" : lrole.trim();
    lrole=(lrole.length()==0) ? null : lrole;
     
    StringBuilder sb=new StringBuilder();
    String sql="";
     
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      sb.append("select item.type, itemitem.lid from item, itemitem where ")
        .append("item.id=itemitem.lid and itemitem.rid=").append(rid);
      if (lrole!=null) sb.append(" and ltype='").append(lrole).append("'");
      
      sql=sb.toString();
      logSql(sql); 
      rs=st.executeQuery(sql);
      while (rs.next())
      {
        String type=rs.getString(1);
        int id=rs.getInt(2);
        return new DefaultItem(id, type);
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
     
    return null;
 }
 
  // Specified in Items.
  public List<Item> getLeftItems(int rid, String lrole) throws IOException
  {
    if (rid<0)
    {
      throw new IllegalArgumentException("rid<0 : "+rid);
    }
   
    lrole=(lrole==null) ? "" : lrole.trim();
    lrole=(lrole.length()==0) ? null : lrole;
   
    List<Item> retVal=new ArrayList<Item>();
   
    StringBuilder sb=new StringBuilder();
    String sql="";
   
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      sb.append("select item.type, itemitem.lid from item, itemitem where ")
        .append("item.id=itemitem.lid and itemitem.rid=").append(rid);
      if (lrole!=null) sb.append(" and ltype='").append(lrole).append("'");
      
      sql=sb.toString();
      logSql(sql);
      rs=st.executeQuery(sql);
      while (rs.next())
      {
        String type=rs.getString(1);
        int id=rs.getInt(2);
        retVal.add(new DefaultItem(id, type));
      }
      
      return retVal;
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }

  //
  
  private static String getQualityPropertyName(String name, String itemType)
  {
    StringBuilder sb=new StringBuilder();
    
    name=name.trim();
    
    itemType=(itemType==null) ? "" : itemType.trim();
    itemType=(itemType.length()==0) ? "item" : itemType;
    
    sb.append(name).append('_').append(itemType);
    
    return sb.toString();
  }
   
  // Specified in Items.
  public boolean defineProperty(String name, String nativeType, Integer flen,
    String itemType) throws IOException
  {
    if (name==null)
    {
      throw new NullPointerException("name");
    }
    
    String qname=getQualityPropertyName(name, itemType);
    if (m_propDefs.containsKey(qname)) return false;
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    try
    {
      st=m_con.createStatement();
      
      sb.setLength(0);
      sb.append("create table ").append(qname)
        .append(" (id int not null, value ").append(nativeType);
      if (flen!=null) sb.append("(").append(flen).append(")");
      sb.append(")");
      sql=sb.toString();
      logSql(sql);
      st.executeUpdate(sql);
      
      sb.setLength(0);
      sb.append("create index pindex on ").append(qname).append(" (id)");
      sql=sb.toString();
      logSql(sql);
      st.executeUpdate(sql);
      
      sb.setLength(0);
      sb.append("insert into propertydef values ('").append(qname)
        .append("', '").append(nativeType).append("', ").append(flen)
        .append(")");
      sql=sb.toString();
      logSql(sql);
      st.executeUpdate(sql);
      
      m_propDefs.put(qname, new PropertyDef(name, nativeType, flen));
      
      return true;
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }

  // Specified in Items.
  public void setProperty(int id, String name, Object value) throws IOException 
  {
    if (name==null)
    {
      throw new NullPointerException("name");
    }
    
    Item item=getItem(id);
    if (item==null)
    {
      throw new IllegalArgumentException("item with id : "+id+
        " does not exists");
    }
    
    String qName=getQualityPropertyName(name, item.getType());
    PropertyDef pd=m_propDefs.get(qName);
    if (pd==null) defineProperty(name, "varchar", 256, item.getType());
    
    StringBuilder sb=new StringBuilder();
    String sql="";
    
    Statement st=null;
    PreparedStatement ps=null;
    try
    {
      st=m_con.createStatement();
      
      sb.setLength(0);
      sb.append("delete from ").append(qName).append(" where id=").append(id);
      sql=sb.toString();
      logSql(sql);
      st.executeUpdate(sql);
      
      if (value!=null)
      {
        sb.setLength(0);
        sb.append("insert into ").append(qName).append(" values (?, ?)");
        sql=sb.toString();
        logSql(sql);
        
        ps=m_con.prepareStatement(sql);
        ps.setInt(1, id);
        ps.setObject(2, value);
        ps.executeUpdate();
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
      if (ps!=null) try { ps.close(); } catch (SQLException _){}
    }
  }

  // Specified in Items.
  public Integer getIntProperty(int id, String name) throws IOException 
  {
    if (id<0)
    {
      throw new IllegalArgumentException("id<0 : "+id);
    }
    
    Item item=getItem(id);
    if (item==null)
    {
      throw new IllegalArgumentException("item with id : "+id+
        " does not exists");
    }
    
    String qName=getQualityPropertyName(name, item.getType());
    PropertyDef pd=m_propDefs.get(qName);
    if (pd==null) return null;
    
    StringBuilder sb=new StringBuilder();
    sb.append("select value from ").append(qName).append(" where id=")
      .append(id);
    String sql=sb.toString();
    
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
        
      logSql(sql);
      rs=st.executeQuery(sql);
      
      if (rs.next())
      {
        int v=rs.getInt(1);
        return rs.wasNull() ? null : v;
      }
      else
      {
        return null;
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
    }
  }
  
  // Specified in Items.
  public String getStringProperty(int id, String name) throws IOException 
  {
    if (id<0)
    {
      throw new IllegalArgumentException("id<0 : "+id);
    }
   
    Item item=getItem(id);
    if (item==null)
    {
      throw new IllegalArgumentException("item with id : "+id+
        " does not exists");
    }
   
    String qName=getQualityPropertyName(name, item.getType());
    PropertyDef pd=m_propDefs.get(qName);
    if (pd==null) return null;
   
    StringBuilder sb=new StringBuilder();
    sb.append("select value from ").append(qName).append(" where id=")
      .append(id);
    String sql=sb.toString();
   
    Statement st=null;
    ResultSet rs=null;
    try
    {
      st=m_con.createStatement();
      
      logSql(sql);
      rs=st.executeQuery(sql);
     
      if (rs.next())
      {
        String v=rs.getString(1);
        return rs.wasNull() ? null : v;
      }
      else
      {
        return null;
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException(sqlx.getLocalizedMessage()+" SQL: "+sql);
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
      if (rs!=null) try { rs.close(); } catch (SQLException _){}
    }
  }
  
  @Override
  public String toString()
  {
    CharArrayPrintWriter capw=new CharArrayPrintWriter();
    
    for (String sql : m_sqlLog) capw.println(sql);
    
    return capw.toString();
  }
  
  /*
  public static void main(String[] args)
  {
    CommandLineParser clp=new CommandLineParser(args);
    
    String user=clp.value("-user", "auser");
    String pass=clp.value("-pass");
    
    try
    {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
      System.out.println("Load the embedded driver");
      Connection conn = null;
      Properties props = new Properties();
      props.put("user", user);
      if (pass!=null) props.put("password", pass);
      // create and connect the database named helloDB 
      conn=DriverManager.getConnection("jdbc:derby:helloDB;create=true", props);
      System.out.println("create and connect to helloDB");
      conn.setAutoCommit(true);
      
      SqlItems items=new SqlItems(conn);
      
      items.dropTables();
      
      //System.exit(0);
      
      items.createTables();
      
      items.defineProperty("name", "varchar", 10, null);
      
      Item item1=items.create();
      System.out.println(item1);
      
      items.setProperty(item1, "name", "Item 1");
      String name=items.getStringProperty(item1, "name");
      System.out.println("Item1's name: "+name);
      
      Item item2=items.create();
      System.out.println(item2);
      
      System.out.println("Item: "+item1+" exists: "+
        items.exists(item1.getId()));
      
      System.out.println("Item: "+item2+" exists: "+
        items.exists(item2.getId()));
      
      System.out.println("Item: "+99+" exists: "+
        items.exists(99));
      
      System.out.println("All items");
      Collection<Item> ims=items.getItems();
      for (Item item : ims)
      {
        System.out.println("  "+item);  
      }
      
      items.createRelation(item1.getId(), item2.getId());
      
      Collection<ItemItem> rel=items.getRelations();
      System.out.println("all relations");
      for (ItemItem curr : rel) 
      {
        System.out.println("  "+curr);  
      }
      
      Item ritem=items.getRightItem(item1);
      System.out.println("right item of "+item1+" : "+ritem);
      
      Collection<Item> ritems=items.getRightItems(item1);
      System.out.println("right items of "+item1+" : "+ritems);
      
      Item litem=items.getLeftItem(item2);
      System.out.println("left item of "+item2+" : "+litem);
      
      Collection<Item> litems=items.getLeftItems(item2);
      System.out.println("left items of "+item2+" : "+litems);
      
      if (items.deleteRelation(item1, item2))
      {
        System.out.println("Did delete relation");
      }
      else
      {
        System.out.println("Did NOT delete relation");
      }
      
      rel=items.getRelations();
      System.out.println("all relations after delete of relation");
      for (ItemItem curr : rel) 
      {
        System.out.println("  "+curr);  
      }
      
      System.exit(0);
    }
    catch (Exception x)
    {
      x.printStackTrace();
      System.exit(-1);
    }
  }
  */
  
  /*
  public static void main(String[] args)
  {
    CommandLineParser clp=new CommandLineParser(args);
    
    String user=clp.value("-user", "auser");
    String pass=clp.value("-pass");
    
    try
    {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
      System.out.println("Load the embedded driver");
      Connection conn = null;
      Properties props = new Properties();
      props.put("user", user);
      if (pass!=null) props.put("password", pass);
      // create and connect the database named helloDB 
      conn=DriverManager.getConnection("jdbc:derby:helloDB;create=true", props);
      System.out.println("create and connect to helloDB");
      conn.setAutoCommit(true);
      
      Tables ct=new Tables(conn);
      
      ct.dropTables();
      
      //System.exit(0);
      
      ct.create();
      
      Items items=new SqlItems(conn);
      
      items.defineProperty("name", "varchar", 10, null);
      
      Item item1=items.create();
      System.out.println(item1);
      
      items.setProperty(item1, "name", "Item 1");
      String name=items.getStringProperty(item1, "name");
      System.out.println("Item1's name: "+name);
      
      Item item2=items.create();
      System.out.println(item2);
      
      System.out.println("Item: "+item1+" exists: "+
        items.exists(item1.getId()));
      
      System.out.println("Item: "+item2+" exists: "+
        items.exists(item2.getId()));
      
      System.out.println("Item: "+99+" exists: "+
        items.exists(99));
      
      System.out.println("All items");
      Collection<Item> ims=items.getItems();
      for (Item item : ims)
      {
        System.out.println("  "+item);  
      }
      
      items.createRelation(item1.getId(), item2.getId());
      
      Collection<ItemItem> rel=items.getRelations();
      System.out.println("all relations");
      for (ItemItem curr : rel) 
      {
        System.out.println("  "+curr);  
      }
      
      Item ritem=items.getRightItem(item1);
      System.out.println("right item of "+item1+" : "+ritem);
      
      Collection<Item> ritems=items.getRightItems(item1);
      System.out.println("right items of "+item1+" : "+ritems);
      
      Item litem=items.getLeftItem(item2);
      System.out.println("left item of "+item2+" : "+litem);
      
      Collection<Item> litems=items.getLeftItems(item2);
      System.out.println("left items of "+item2+" : "+litems);
      
      if (items.deleteRelation(item1, item2))
      {
        System.out.println("Did delete relation");
      }
      else
      {
        System.out.println("Did NOT delete relation");
      }
      
      rel=items.getRelations();
      System.out.println("all relations after delete of relation");
      for (ItemItem curr : rel) 
      {
        System.out.println("  "+curr);  
      }
      
      System.exit(0);
    }
    catch (Exception x)
    {
      x.printStackTrace();
      System.exit(-1);
    }
  }
  */

}
